package org.wheel.plugins.toolkit.jdbc.pool.util.excution;


import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Pair;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.wheel.module.core.util.SplitUtil;
import org.wheel.plugins.toolkit.jdbc.pool.bean.DatabaseConnection;
import org.wheel.plugins.toolkit.jdbc.pool.util.DynamicJdbcPoolUtil;
import org.wheel.plugins.toolkit.jsqlparse.helper.ParamValueConvert;
import org.wheel.plugins.toolkit.sql.core.GlobalConfig;
import org.wheel.plugins.toolkit.sql.core.LambdaInsertWrapper;
import org.wheel.plugins.toolkit.sql.enums.SqlModel;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * 更新/删除专属 Executor
 */
public class SqlUpdateExecutor {

    private final DynamicJdbcPoolUtil dynamicJdbcPool = DynamicJdbcPoolUtil.getInstance();

    // 直接使用 LogFactory 创建日志对象，Hutool 会自动检测项目中的日志框架（优先级：Slf4j > Log4j > Logback > JDK Logging）
    private static final Log log = LogFactory.get();

    /* ================== 命名参数风格 (:name) ================== */

    public int update(DatabaseConnection conn, String sql, LinkedHashMap<String, Object> param, SqlModel model) {
        SqlModelResolver.ResolvedSql rs = SqlModelResolver.resolve(sql, param, model);
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        if (rs.getParamSource() != null) {
            NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
            log.debug("SQL: {}, param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
            return named.update(rs.getFinalSql(), rs.getParamSource());
        }
        // NATIVE / DIRECT 走数组
        log.debug("SQL: {}, param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
        return tpl.update(rs.getFinalSql(), rs.getArgs());
    }
    // 批量更新
    public int[] batchUpdate(DatabaseConnection conn, String sql, LinkedHashMap<String, Object>[] batchPairs, SqlModel model) {
        List<SqlModelResolver.ResolvedSql> rsList = Arrays.stream(batchPairs)
                .map(param->SqlModelResolver.resolve(sql, param, model)).toList();
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        String rsSql = rsList.get(0).getFinalSql();
        int[] resultInt = new int[0];
        if(CollectionUtil.isNotEmpty(rsList)){
            List<List<SqlModelResolver.ResolvedSql>> splite = SplitUtil.split(rsList, 1000);
            for(List<SqlModelResolver.ResolvedSql> list: splite) {
                NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
                MapSqlParameterSource[] paramArr = list.stream().map(SqlModelResolver.ResolvedSql::getParamSource).toArray(MapSqlParameterSource[]::new);
                int[] tmpResult = named.batchUpdate(rsSql, paramArr);
                resultInt = IntStream.concat(Arrays.stream(resultInt), Arrays.stream(tmpResult)).toArray();
            }
        }
        return resultInt;
    }

    public int[] batchUpdate(DatabaseConnection conn, Collection<SqlTuple<String, LinkedHashMap<String,Object>, SqlModel>> batchPairs){
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        List<SqlModelResolver.ResolvedSql> rsList = batchPairs.stream()
                .map(tuple->SqlModelResolver.resolve(tuple.getSql(), tuple.getParams(), tuple.getSqlModel())).toList();
        return batchExecute(tpl, rsList);
    }

    public int[] batchUpdate(DatabaseConnection conn, Collection<Pair<String, LinkedHashMap<String, Object>>> batchPairs, SqlModel model){
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        List<SqlModelResolver.ResolvedSql> rsList = batchPairs.stream()
                .map(pair->SqlModelResolver.resolve(pair.getKey(), pair.getValue(), model)).toList();
        return batchExecute(tpl, rsList);
    }

    private int[] batchExecute(JdbcTemplate tpl, List<SqlModelResolver.ResolvedSql> rsList){
        if(CollectionUtil.isNotEmpty(rsList)){
            // 批量执行, 避免批量执行SQL语句过多
            List<List<SqlModelResolver.ResolvedSql>> splite = SplitUtil.split(rsList, 1000);
            int[] resultInt = new int[0];
            for(List<SqlModelResolver.ResolvedSql> list: splite) {
                NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
                // 将rsList集合按 SQL语句进行分组
                Map<String, List<SqlModelResolver.ResolvedSql>> rsSqlMap = list.stream()
                        .collect(Collectors.groupingBy(SqlModelResolver.ResolvedSql::getFinalSql));
                int[] tmpResult = rsSqlMap.entrySet().stream().map(entry -> {
                    return named.batchUpdate(entry.getKey(), entry.getValue().stream()
                            .map(SqlModelResolver.ResolvedSql::getParamSource).toArray(MapSqlParameterSource[]::new));
                }).flatMapToInt(IntStream::of).toArray();
                resultInt = IntStream.concat(Arrays.stream(resultInt), Arrays.stream(tmpResult)).toArray();
            }
            return resultInt;
        }
        return new int[0];
    }

    /** ================== 原生 ? 占位符风格 ================== */
    public int nativeUpdate(DatabaseConnection conn, String sql, Object... args) {
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        log.debug("SQL: {}, param: {}\nexec SQL: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
        return tpl.update(sql, args);
    }

    /** ================== 命名参数风格 (:name) ================== */
    public int namedParameterUpdate(DatabaseConnection conn, String sql, Map<String, Object> args) {
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
        log.debug("SQL: {}, param: {}\nexec SQL: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
        return named.update(sql, args);
    }

    /** ================== 原生 无占位符 风格 ================== */
    public int nativeUpdate(DatabaseConnection conn, String sql) {
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        log.debug("SQL: {}", sql);
        return tpl.update(sql);
    }


    /**
     * 执行 INSERT 语句并返回数据库生成的主键
     * @param conn 数据库连接
     * @param sql SQL 语句
     * @param params 参数
     * @param sqlModel SQL 模型
     * @param <T> 返回的主键类型
     * @return 生成的主键值
     */
    public <T> T insertWithGeneratedKeys(DatabaseConnection conn,
                                         String sql,
                                         LinkedHashMap<String, LambdaInsertWrapper.InsertParam> params,
                                         SqlModel sqlModel){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
            Connection connection = tpl.getDataSource().getConnection();

            // 关键：使用 Statement.RETURN_GENERATED_KEYS
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            // 设置参数
            setParameters(ps, params);

            // 执行插入
            int affectedRows = ps.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Creating user failed, no rows affected.");
            }

            // 获取生成的主键
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                // 假设主键是单一的
                Object key = rs.getObject(1);
                // 这里可以添加类型转换逻辑，例如根据 entityClass 推断主键类型
                return (T) key;
            } else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error executing insert with generated keys", e);
        } finally {
            closeQuietly(rs);
            closeQuietly(ps);
        }
    }
    /**
     * 为 PreparedStatement 设置参数
     * @param ps PreparedStatement
     * @param params 参数映射
     * @throws SQLException
     */
    /**
     * 为 PreparedStatement 设置参数，基于类型信息
     * @param ps PreparedStatement
     * @param params 参数映射，包含值和类型
     * @throws SQLException
     */
    private void setParameters(PreparedStatement ps, LinkedHashMap<String, LambdaInsertWrapper.InsertParam> params) throws SQLException {
        int index = 1;
        for (LambdaInsertWrapper.InsertParam param : params.values()) {
            Object value = param.getValue();
            Class<?> type = param.getFieldType();

            if (value == null) {
                // 如果值为 null，可以根据类型推断 SQL 类型
                int sqlType = getSqlTypeFromJavaType(type);
                ps.setNull(index, sqlType);
            } else {
                // 根据 Java 类型设置参数
                setTypedParameter(ps, index, value, type);
            }
            index++;
        }
    }

    /**
     * 根据 Java 类型设置参数
     * @param ps PreparedStatement
     * @param index 参数索引
     * @param value 值
     * @param type Java 类型
     * @throws SQLException
     */
    private void setTypedParameter(PreparedStatement ps, int index, Object value, Class<?> type) throws SQLException {
        // 基本类型及其包装类
        if (type == int.class || type == Integer.class) {
            ps.setInt(index, (Integer) value);
        } else if (type == long.class || type == Long.class) {
            ps.setLong(index, (Long) value);
        } else if (type == short.class || type == Short.class) {
            ps.setShort(index, (Short) value);
        } else if (type == byte.class || type == Byte.class) {
            ps.setByte(index, (Byte) value);
        } else if (type == boolean.class || type == Boolean.class) {
            ps.setBoolean(index, (Boolean) value);
        } else if (type == float.class || type == Float.class) {
            ps.setFloat(index, (Float) value);
        } else if (type == double.class || type == Double.class) {
            ps.setDouble(index, (Double) value);
        } else if (type == char.class || type == Character.class) {
            ps.setString(index, String.valueOf(value)); // char 通常映射为 String
        } else if (type == String.class) {
            ps.setString(index, (String) value);
        } else if (type == java.sql.Date.class) {
            ps.setDate(index, (java.sql.Date) value);
        } else if (type == java.util.Date.class) {
            // java.util.Date 需要转换为 java.sql.Date 或 Timestamp
            if (value instanceof java.util.Date) {
                ps.setTimestamp(index, new java.sql.Timestamp(((java.util.Date) value).getTime()));
            } else {
                ps.setObject(index, value); // fallback
            }
        } else if (type == java.time.LocalDate.class) {
            ps.setObject(index, value); // JDBC 4.2+ 支持 java.time
        } else if (type == java.time.LocalDateTime.class) {
            ps.setObject(index, value); // JDBC 4.2+ 支持 java.time
        } else {
            // 对于未知类型，使用 setObject，让 JDBC 驱动处理
            ps.setObject(index, value);
        }
    }

    /**
     * 根据 Java 类型推断 SQL 类型 (java.sql.Types 常量)
     * @param javaType Java 类型
     * @return 对应的 SQL 类型
     */
    private int getSqlTypeFromJavaType(Class<?> javaType) {
        if (javaType == int.class || javaType == Integer.class) {
            return java.sql.Types.INTEGER;
        } else if (javaType == long.class || javaType == Long.class) {
            return java.sql.Types.BIGINT;
        } else if (javaType == short.class || javaType == Short.class) {
            return java.sql.Types.SMALLINT;
        } else if (javaType == byte.class || javaType == Byte.class) {
            return java.sql.Types.TINYINT;
        } else if (javaType == boolean.class || javaType == Boolean.class) {
            return java.sql.Types.BOOLEAN;
        } else if (javaType == float.class || javaType == Float.class) {
            return java.sql.Types.REAL;
        } else if (javaType == double.class || javaType == Double.class) {
            return java.sql.Types.DOUBLE;
        } else if (javaType == char.class || javaType == Character.class || javaType == String.class) {
            return java.sql.Types.VARCHAR;
        } else if (javaType == java.sql.Date.class) {
            return java.sql.Types.DATE;
        } else if (javaType == java.util.Date.class) {
            return java.sql.Types.TIMESTAMP; // 通常映射为 Timestamp
        } else if (javaType == java.time.LocalDate.class) {
            return java.sql.Types.DATE;
        } else if (javaType == java.time.LocalDateTime.class) {
            return java.sql.Types.TIMESTAMP;
        }
        // 默认返回 OTHER 或 VARCHAR
        return java.sql.Types.OTHER;
    }

    /**
     * 安静地关闭 ResultSet
     * @param rs ResultSet
     */
    private void closeQuietly(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // 日志记录
                e.printStackTrace(); // 生产环境应使用日志框架
            }
        }
    }

    /**
     * 安静地关闭 Statement (PreparedStatement)
     * @param stmt Statement
     */
    private void closeQuietly(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                // 日志记录
                e.printStackTrace(); // 生产环境应使用日志框架
            }
        }
    }
}
